﻿using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace MisBasic.Dao.Helper
{
    public class CHelper<T> where T : new()
    {
        private string connectionString = "";
        private DataProvider providerType;

        public CHelper()
        {
            connectionString = Common.GetConnectionString();
            providerType = Common.GetDataProvider();
        }

        public string ExecuteSql(string sql)
        {
            Int32 iRows = 0;
            using (IDbConnection conn = DBManager.GetConnection(providerType, connectionString))
            {
                conn.Open();
                IDbCommand cmd = DBManager.GetCommand(providerType);
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                iRows = cmd.ExecuteNonQuery();
            }
            return iRows.ToString();
        }

        public T GetTById(String sql)
        {
            T t = new T();
            Type type = typeof(T);
            DataTable dt = new DataTable();
            using (IDbConnection conn = DBManager.GetConnection(providerType, connectionString))
            {
                conn.Open();
                IDbCommand cmd = DBManager.GetCommand(providerType);
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                IDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    PropertyInfo[] propertys = t.GetType().GetProperties();
                    foreach (PropertyInfo pi in propertys)
                    {
                        int currFieldIndex = 0;
                        bool isConains = false;
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            if (dr.GetName(i).ToUpper() == pi.Name.ToUpper())
                            {
                                isConains = true;
                                currFieldIndex = i;
                                break;
                            }
                        }
                        if (isConains)
                        {
                            if (!pi.CanWrite) continue;
                            object value = dr[currFieldIndex];
                            if (value != DBNull.Value)
                                pi.SetValue(t, value, null);
                        }
                    }
                }
            }
            return t;
        }

        public String GetById(String sql)
        {
            return JsonConvert.SerializeObject(GetTById(sql));
        }

        public List<T> GetTList(String sql)
        {
            List<T> dataList = new List<T>();
            Type type = typeof(T);

            DataTable dt = new DataTable();
            using (IDbConnection conn = DBManager.GetConnection(providerType, connectionString))
            {
                conn.Open();
                IDbCommand cmd = DBManager.GetCommand(providerType);
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                IDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    T t = new T();
                    PropertyInfo[] propertys = t.GetType().GetProperties();
                    foreach (PropertyInfo pi in propertys)
                    {
                        int currFieldIndex = 0;
                        bool isConains = false;
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            if (dr.GetName(i).ToUpper() == pi.Name.ToUpper())
                            {
                                isConains = true;
                                currFieldIndex = i;
                                break;
                            }
                        }
                        if (isConains)
                        {
                            if (!pi.CanWrite) continue;
                            object value = dr[currFieldIndex];
                            if (value != DBNull.Value)
                                pi.SetValue(t, value, null);
                        }
                    }
                    dataList.Add(t);
                }
            }
            return dataList;
        }

        protected String GetList(string sql)
        {
            return JsonConvert.SerializeObject(GetTList(sql));
        }

        public bool IsExist(string sql)
        {
            using (IDbConnection conn = DBManager.GetConnection(providerType, connectionString))
            {
                conn.Open();
                IDbCommand cmd = DBManager.GetCommand(providerType);
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                IDataReader dr = cmd.ExecuteReader();
                dr.Read();
                //Int32 iCount = dr.GetInt32(0);
                long iCount = dr.GetInt64(0);

                if (iCount == 0)
                    return false;
                else
                    return true;
            }
        }

        public String GetSequenceNumber(String beginStr, String tableName, int numberLength)
        {
            String docNumber = "";
            Int32 id = InsertSequence(tableName);
            for (int i = 0; i < numberLength; i++)
            {
                docNumber += "0";
            }
            docNumber += id.ToString().Trim();
            return beginStr + docNumber.Substring(id.ToString().Trim().Length, docNumber.Length - id.ToString().Trim().Length);

        }

        private int InsertSequence(String tableName)
        {
            String id = Guid.NewGuid().ToString("D");
            using (IDbConnection conn = DBManager.GetConnection(providerType, connectionString))
            {
                conn.Open();
                IDbCommand cmd = DBManager.GetCommand(providerType);
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = String.Format(@"INSERT INTO [{0}]
                                                               ([Guid])
                                                         VALUES
                                                               ('{1}');SELECT @@IDENTITY"
                                                                , tableName
                                                                , id);
                IDataReader dr = cmd.ExecuteReader();
                dr.Read();
                return Convert.ToInt32(dr[0].ToString());
            }
        }
    }
}
